##!/usr/bin/env python
##-*- coding: utf-8 -*-
"""

*    convertcsv.py
*    This script makes reports from ufw firewall log
*    Report type can be ,csv and 
*    Mestrado em Engenharia de Segurança Informática
*    Escola Superior de tecnologia e Gestão de Beja
*    
*    Copyright 2013 Andreia Graça <198912823@alunos.ipbeja.pt>
*    Copyright 2013 Palmira Martins <198913316@alunos.ipbeja.pt>
*    
*    This program is an academic software.
*    Only for academic purposes. Under free GNU Licences.
"""     
import sys #system calls
import re # re.search
import GeoIP #GeoIP
import sqlite3 #database sqlite3
import datetime 
import csv #save report
import optparse #arguments/option parse
import os
import socket
import string


##Open GEOIP data files for ipv4
gic = GeoIP.open('./Geo/GeoIPCity.dat', GeoIP.GEOIP_STANDARD)
##Open GEOIP data files for ipv6
giv6 = GeoIP.open('./Geo/GeoIPv6.dat', GeoIP.GEOIP_MEMORY_CACHE)

def openLog(logfile):
    """
    Function openLog(logfile)
    Receive argument log file ant try to open in read mode
    Exception if could not open the file
    """
    try:
        log=open(logfile, 'r')
        return log
    except Exception, e:
        print "[-] " + str(e)
        sys.exit()

def readLog(log):
    """
    Function readLog(log)
    Read log file and save the important fields in a sqlite3 database
    """
    con=createDb()
    con.cursor()
    con.execute("DROP TABLE IF EXISTS GEOIP")
    con.execute("CREATE TABLE GEOIP(Id integer,Time DATA ,IPsrc TEXT,EventSrc TEXT,Device TEXT,Protocol TEXT,TTL TEXT, SRCPort TEXT, DSTPort TEXT,Country TEXT,CountryName TEXT,Lon TEXT,Lat TEXT, CheckBL TEXT)")
    id=1
    
    for line in log.readlines():
        debug = 0
        if not re.search("SRC=192", line) and not re.search("SRC=0", line) and not re.search("SRC=172", line):
            #IP SRC
            lista = line.split("SRC=")
            IP = lista[1].split(' ')[0]
            #DATA
            lineMonth = str(line)
            dataT = lineMonth[:15]
            #INTERFACE
            device = line.split("IN=")
            dInterface = device[1].split(' ')[0]
            if (str(dInterface) ==""):
                device = line.split("OUT=")
                dInterface = device[1].split(' ')[0]
                #EVENT SOURCE
                event = line.split("IN= ")
                eventSrc = event[1].split('=')[0]
            else:
                eventSrc = "IN"

            #PROTO
            proto=line.split("PROTO=")
            protoInf=proto[1].split(' ')[0]

            try:
                if len(IP)<=15:
                    #SOURCE PORT
                    spt=line.split("SPT=")
                    sptPort=spt[1].split(' ')[0]
                    #DESTINATION PORT
                    dpt=line.split("DPT=")
                    dptPort=dpt[1].split(' ')[0]
                    data=gic.record_by_addr(IP)
                    cty = data['country_code']
                    ctyName =data['country_name']
                    longitude = data['longitude']
                    latitude = data['latitude']
                    ttlinf=line.split("TTL=")
                    ttlData=str(ttlinf[1].split(' ')[0])
                else:
                    cty=giv6.country_code_by_addr_v6(IP)
                    
                con.execute("INSERT INTO GEOIP VALUES (:Id,:Time,:IPsrc,:EventSrc,:Device,:Protocol,:TTL,:SRCPort,:DSTPort,:Country,:CountryName,:Lon,:Lat,:CheckBL)", {"Id":id,"Time":dataT,"IPsrc":IP,"EventSrc":eventSrc,"Device":dInterface,"Protocol":protoInf,"TTL":ttlData,"SRCPort":sptPort,"DSTPort":dptPort,"Country":cty,"CountryName":ctyName,"Lon":longitude,"Lat":latitude,"CheckBL":""})
                id+=1

            except:
                continue            
    con.commit()
    
    if con:
        con.close()

def createDb():
    """
    Function createDb()
    Creates a sqlite database in directory "./db/" with "ldp.db" name
    Exception lite.Error if cannot create the database
    """
    try:
        con = sqlite3.connect('./db/lpd.db')
        cur = con.cursor()
        return con
    except lite.Error, e:
        print "Error %s:" % e.args[0]
        sys.exit(1)
   
   

def createCsv():
    """
    Function createCsv() - Create a CSV file Report
    Creates the csv Report with recursion of library csv
    """
    now = datetime.datetime.now() #datetime for filename
    try:
        con = sqlite3.connect('./db/lpd.db') #connection to database
        cur = con.cursor() # connection cursor
        csvFileName="csvFile_%s.%s.%s.csv" % (now.day, now.month, now.year)
    except Exception, e:
        print "- "+e
    data = cur.execute("SELECT Id, Time, IPsrc, EventSrc, Device, Protocol, SrcPort, DstPort, Country, CountryName, Lon, Lat  CheckBL FROM GEOIP")
    out = csv.writer(open(csvFileName,"w"), delimiter=',',quoting=csv.QUOTE_NONE)
    out.writerow(('Id', 'Time', 'IPsrc', 'EventSrc', 'Device', 'Protocol', 'SrcPort', 'DstPort','Country', 'Country Name', 'Longitude', 'Latitude'))
    for x in data:
        out.writerow(x)
    if con:
        con.close()
    print "Done!\nCsv file created with filename "+ csvFileName





def main():
    """
    main funtion
    first openLog file function and readLog function
    call function creatCsv() if this the type of argument is given
    arguments obtained with optparse
    """
    parser = optparse.OptionParser(usage="Usage: %prog " +"-f <log file> -c (export to csv) ", version='%prog 0.01 - MESI2013')
    parser.add_option("-f", dest="file", type="string", help="name of the log file")
    parser.add_option("-c", "--csv", action="store_true", dest="csv", help="create a Report in CSV file")
    
    (options, args) = parser.parse_args()

    if options.file:
        print "... (please wait)"
        savelog=openLog(options.file)
        readLog(savelog)
    else:
        print "You must specify a log file."
        print ("Usage: -h for help")
        exit(0)

    if options.csv == True:
        createCsv()
   
       

if __name__ == '__main__':
    main()
